<?php

class Tour_Model_Tour extends Zend_Db_Table_Abstract
{

    public function __construct($config = array())
    {
        $this->_db = Zend_Db_Table::getDefaultAdapter();
        parent::__construct($config);
    }

    public function selectOrderList()
    {   
        
    }
    public function getTourLeftLimit($idTour){
        $select = $this->_db->select()
                            ->from(array('td'=>'tour_description'),
                                      array('LEFT_LIMIT'=>'left_limit_of_reservation'))
                            ->join(array('tct' => 'tourist_company_tour'),
                                         'tct.id = td.id_tour',
                                         array('AVAILABLE'=>'available')
                                        )
                            ->where('tct.id = ?',$idTour);
        $count = $this->_db->fetchAll($select);
        return $count[0]['LEFT_LIMIT'];
    }
    public function countSearchList($search){
        $select = $this->_db->select()
                            ->from(array('tct'=>'tourist_company_tour'),
                                      array('COUNT(*) as COUNT'))
                            ->group('tct.id_company')
                            ->join(array('td' => 'tour_description'),
                                         'tct.id = td.id_tour',
                                         array('TOUR_NAME'=>'name')
                                        )
                            ->join(array('hc' => 'helium_city'),
                                         'td.start_place = hc.id',
                                         array('NAME'=>'name')
                                        );
        $select->where('td.left_limit_of_reservation > 0');
        if($search['CITY'] > 0){
            $select->where('hc.id = ? ',$search['CITY']);
        }
        if($search['LOAITOUR'] > 0 ){
            $select->where('tct.id_tour_category = ? ',$search['LOAITOUR']);
        }
        if($search['STARTDATE'] != ""){
            $select->where('td.start_date = ? ',common::convertToSystemDate($search['STARTDATE']));
        }
        
        $count = $this->_db->fetchAll($select);
        return $count[0]['COUNT'];
    }
    public function countRegistryList($idCompany){
        $selectCount = $this->_db->select()
                                 ->from(array('tct'=>'tourist_company_tour'),
                                     array('COUNT' =>'count(*)'))
                                 ->where('tct.id_company = ? ',$idCompany);
        $count = $this->_db->fetchAll($selectCount);
        return $count[0]['COUNT'];
    }
    
    public function getTourInfo($id)
    {
        $select = $this->_db->select()
                            ->from(array('tct'=>'tourist_company_tour'),
                                     array(
                                     'CODE'=>'code',
                                     'AVAILABLE'=>'available',
                                     'CATEGORY'=>'id_tour_category',
                                     'NUM_DAY'=>'number_of_day',
                                     'NUM_NIGHT'=>'number_of_night',
                                     'ID_COMPANY'=>'id_company',
                                     'ID_TOUR'=>'id'
                                     )
                                     )
                            ->join(
                                    array('td' => 'tour_description'),'tct.id = td.id_tour',
                                    array('TOUR_NAME'=>'name',
                                    'IMAGE'=>'images',
                                    'JOURNEY'=>'journey',
                                    'LEFT_LIMIT'=>'left_limit_of_reservation',
                                    'TRANSPORT'=>'transport',
                                    'PRICE_CHILDREN'=>'price_children',
                                    'PRICE_ADULT'=>'price_adult',
                                    'START_DATE'=>'start_date'
                                    )
                                  )
                            ->join(
                                    array('tc' => 'tour_category'),'tct.id_tour_category = tc.id',array('TOUR_CATEGORY'=>'name')
                                  )
                            ->join(
                                    array('c' => 'company'),'tct.id_company = c.id',array('COMPANY'=>'name')
                                  )
                            ->join(
                                    array('hc' => 'helium_city'),'td.start_place = hc.id',array('CITY_NAME'=>'name')
                                  );
        if($search['CITY'] > 0){
            $select->where('tct.id = ? ', $id );
        }
        
        $data = $this->_db->fetchAll($select);
        return $data[0];
    }
    public function search($page,$search)
    {
        $offset = $page * 10;
        $select = $this->_db->select()
                            ->from(array('tct'=>'tourist_company_tour'),
                                     array('CODE'=>'code','AVAILABLE'=>'available','CATEGORY'=>'id_tour_category','ID_TOUR'=>'id'))
                            ->join(
                                    array('td' => 'tour_description'),'tct.id = td.id_tour',array('TOUR_NAME'=>'name','IMAGE'=>'images','JOURNEY'=>'journey')
                                  )
                            ->join(
                                    array('c' => 'company'),'tct.id_company = c.id',array('COMPANY'=>'name')
                                  )
                            ->join(
                                    array('hc' => 'helium_city'),'td.start_place = hc.id',array('NAME'=>'name')
                                  );
        $select->where('td.left_limit_of_reservation > 0');
        if($search['CITY'] > 0){
            $select->where('hc.id = ? ',$search['CITY']);
        }
        if($search['LOAITOUR'] > 0 ){
            $select->where('tct.id_tour_category = ? ',$search['LOAITOUR']);
        }
        if($search['STARTDATE'] != ""){
            $select->where('td.start_date = ? ',common::convertToSystemDate($search['STARTDATE']));
        }
        if($search['PRICE'] > 0 ){
            $arrPrice = array(
                0 => '',
                1 => ' td.price_adult <= 2000000',
                2 => ' td.price_adult >= 2000000 AND td.price_adult < 4000000 ',
                3 => ' td.price_adult >= 4000000 AND td.price_adult < 6000000 ',
                4 => ' td.price_adult >= 6000000 AND td.price_adult < 10000000 ',
                5 => ' td.price_adult >= 10000000'
            );
            $select->where($arrPrice[$search['PRICE']]);
        }
        
        $select->order(array('td.start_date DESC'))
               ->limit(10,$offset);
        $data = $this->_db->fetchAll($select);
        return $data;
    }
    public function selectRegistryList($idCompany,$page,$search)
    {
        $offset = $page * 10;
        $select = $this->_db->select()
                            ->from(array('tct'=>'tourist_company_tour'),
                                     array('CODE'=>'code','AVAILABLE'=>'available','CATEGORY'=>'id_tour_category'))
                            ->join(array('td' => 'tour_description'),
                                         'tct.id = td.id_tour',
                                         array('TOUR_NAME'=>'name')
                                        )
                            ->join(array('tc' => 'tour_category'),
                                         'tct.id_tour_category = tc.id',
                                         array('CATEGORY_NAME'=>'name')
                                        )
                            ->where('tct.id_company = ? ',$idCompany);
        
        if($search['FROMDATE'] != ""){
            $select->where('tct.date_create >= ? ',$search['FROMDATE']);
        }
        if($search['TODATE'] != ""){
            $select->where('tct.date_create <= ? ',$search['TODATE']);
        }
        
        $select->order(array('tct.id_tour_category DESC'))
               ->limit(10,$offset);
        $data = $this->_db->fetchAll($select);
        return $data;
    }
}

